package org.example.managementsystem.repository;

import org.example.managementsystem.model.Device;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;
import java.util.Objects;

/**
 * 设备数据访问层
 */
@Repository
public class DeviceDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public DeviceDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 更新设备编号
     */
    public int updateDeviceNumber(Long id, String deviceNumber) {
        String sql = "UPDATE device SET device_number = ? WHERE id = ?";
        return jdbcTemplate.update(sql, deviceNumber, id);
    }

    /**
     * 批量更新空设备编号
     * 根据设备类型自动生成设备编号
     */
    public int batchUpdateEmptyDeviceNumbers() {
        String sql = "UPDATE device SET device_number = " +
                "CASE device_type " +
                "WHEN '重型' THEN CONCAT('ZX', id) " +
                "WHEN '中型' THEN CONCAT('ZX', id) " +
                "WHEN '轻型' THEN CONCAT('QX', id) " +
                "WHEN '便携式' THEN CONCAT('BX', id) " +
                "WHEN '其它' THEN CONCAT('QT', id) " +
                "END " +
                "WHERE device_number IS NULL OR device_number = ''";
        
        return jdbcTemplate.update(sql);
    }

    /**
     * 添加设备
     */
    public Long insertDevice(Device device) {
        String sql = "INSERT INTO device (device_number, device_name, device_type, manufacturer, " +
                "manufacture_date, warranty_period, after_sales_contact, after_sales_phone, warranty_desc) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        KeyHolder keyHolder = new GeneratedKeyHolder();
        
        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, device.getDeviceNumber());
            ps.setString(2, device.getDeviceName());
            ps.setString(3, device.getDeviceType());
            ps.setString(4, device.getManufacturer());
            ps.setDate(5, device.getManufactureDate() != null ? 
                    Date.valueOf(device.getManufactureDate()) : null);
            ps.setString(6, device.getWarrantyPeriod());
            ps.setString(7, device.getAfterSalesContact());
            ps.setString(8, device.getAfterSalesPhone());
            ps.setString(9, device.getWarrantyDesc());
            return ps;
        }, keyHolder);
        
        return Objects.requireNonNull(keyHolder.getKey()).longValue();
    }

    /**
     * 更新设备
     */
    public int updateDevice(Device device) {
        String sql = "UPDATE device SET device_number = ?, device_name = ?, device_type = ?, " +
                "manufacturer = ?, manufacture_date = ?, warranty_period = ?, " +
                "after_sales_contact = ?, after_sales_phone = ?, warranty_desc = ? " +
                "WHERE id = ?";
        
        return jdbcTemplate.update(sql,
                device.getDeviceNumber(),
                device.getDeviceName(),
                device.getDeviceType(),
                device.getManufacturer(),
                device.getManufactureDate() != null ? Date.valueOf(device.getManufactureDate()) : null,
                device.getWarrantyPeriod(),
                device.getAfterSalesContact(),
                device.getAfterSalesPhone(),
                device.getWarrantyDesc(),
                device.getId());
    }

    /**
     * 删除设备
     */
    public int deleteDevice(Long id) {
        String sql = "DELETE FROM device WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    /**
     * 根据ID查询设备
     */
    public Device getDeviceById(Long id) {
        String sql = "SELECT * FROM device WHERE id = ?";
        List<Device> devices = jdbcTemplate.query(sql, new DeviceRowMapper(), id);
        return devices.isEmpty() ? null : devices.get(0);
    }

    /**
     * 查询所有设备
     */
    public List<Device> getAllDevices() {
        String sql = "SELECT * FROM device";
        return jdbcTemplate.query(sql, new DeviceRowMapper());
    }

    /**
     * 根据条件查询设备
     */
    public List<Device> searchDevices(String keyword) {
        String sql = "SELECT * FROM device WHERE device_number LIKE ? " +
                "OR device_name LIKE ? OR manufacturer LIKE ?";
        
        String searchParam = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new DeviceRowMapper(), 
                searchParam, searchParam, searchParam);
    }

    /**
     * 设备行映射器
     */
    private static class DeviceRowMapper implements RowMapper<Device> {
        @Override
        public Device mapRow(ResultSet rs, int rowNum) throws SQLException {
            Device device = new Device();
            device.setId(rs.getLong("id"));
            device.setDeviceNumber(rs.getString("device_number"));
            device.setDeviceName(rs.getString("device_name"));
            device.setDeviceType(rs.getString("device_type"));
            device.setManufacturer(rs.getString("manufacturer"));
            
            Date manufactureDate = rs.getDate("manufacture_date");
            if (manufactureDate != null) {
                device.setManufactureDate(manufactureDate.toLocalDate());
            }
            
            device.setWarrantyPeriod(rs.getString("warranty_period"));
            device.setAfterSalesContact(rs.getString("after_sales_contact"));
            device.setAfterSalesPhone(rs.getString("after_sales_phone"));
            device.setWarrantyDesc(rs.getString("warranty_desc"));
            
            return device;
        }
    }
}
